﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using ERP_RORZE_SYSTEM;
using System.Globalization;
using System.Threading;

namespace ERP_RORZE_DLL.Assembly
{
    public partial class frm_Edit_AssemblyCheck : Form
    {
        private INISettings mySetting;
        private SqlConnection myConnection;

        public ComboboxItem checkType;
        public string serial;
        
        private List<intem_AssemblyCheck> itemList;
        private List<intem_AssemblyCheckDate> itemListDate;
        private List<intem_AssemblyCheckSerial> itemListSerial;

        private List<string> SubSerialList;
        private List<string> StaffList;

        private DataSet ds;

        private string main_serial;

        public frm_Edit_AssemblyCheck()
        {
            InitializeComponent();
            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);

            this.checkType = new ComboboxItem();
            this.itemList = new List<intem_AssemblyCheck>();
            this.itemListSerial = new List<intem_AssemblyCheckSerial>();
            this.itemListDate = new List<intem_AssemblyCheckDate>();
            this.SubSerialList = new List<string>();
            this.StaffList = new List<string>();
            this.ds = new DataSet("RobotOrderDetail");
            this.main_serial = null;
            this.serial = null;            
        }

        /// <summary>
        /// Mapping Data
        /// </summary>
        public bool MappingData()
        {
            bool ret = true;
            if (string.IsNullOrEmpty(this.serial))
            {
                ret = false;
            }
            else
            {
                try
                {
                    myConnection.Open();
                    SqlCommand cmd_sql = myConnection.CreateCommand();
                    cmd_sql.CommandText = "SELECT * FROM [231_CheckDetails]"
                        + " WHERE RRCSerialNo = '" + this.serial + "'"
                        + " AND CheckTypeID = " + this.checkType.Value.ToString();
                    SqlDataReader dataReader = cmd_sql.ExecuteReader();
                    while (dataReader.Read())
                    {
                        for (int i = 0; i < this.itemListSerial.Count; i++)
                        {
                            if (this.itemListSerial[i].funcID == Int32.Parse(dataReader["CheckFuntionID"].ToString()))
                            {
                                this.itemListSerial[i].setValue(dataReader["CheckValue"].ToString());
                            }
                        }

                        for (int i = 0; i < this.itemListDate.Count; i++)
                        {
                            if (this.itemListDate[i].funcID == Int32.Parse(dataReader["CheckFuntionID"].ToString()))
                            {
                                if (dataReader["CheckValue"] != DBNull.Value)
                                {
                                    DateTime dt = DateTime.ParseExact(dataReader["CheckValue"].ToString(), "yyyyMMdd", CultureInfo.InvariantCulture);
                                    this.itemListDate[i].setValue(dt);
                                }                                
                            }
                        }

                        for (int i = 0; i < this.itemList.Count; i++)
                        {
                            if (this.itemList[i].funcID == Int32.Parse(dataReader["CheckFuntionID"].ToString()))
                            {
                                if (this.itemList[i].defaultvalue != dataReader["CheckValue"].ToString().Trim())
                                {
                                    this.itemList[i].setValue(dataReader["CheckValue"].ToString());
                                }                                
                            }
                        }
                    }
                    dataReader.Close();
                    cmd_sql.Dispose();
                    myConnection.Close();
                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show(ex.Message);
                    ret = false;
                }
            }
            return ret;
        }

        private void LoadSerialList()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT [SerialNo], [CompID] FROM [231_RobotOrderDetail]"
                    + " WHERE (FinishedDate > DateAdd(dd, 0, DateDiff(dd, 0, GETDATE())) - 500) AND [SerialNo] NOT IN"
                    + " (SELECT [RRCSerialNo] AS [SerialNo] FROM [231_CheckDetails]"
                    + " WHERE [RRCSerialNo] <> '" + this.serial + "' GROUP BY [RRCSerialNo])";
                SqlDataAdapter da1 = new SqlDataAdapter();
                da1.SelectCommand = cmd_sql;
                da1.TableMappings.Add("Table", "RobotOrderDetail");
                da1.Fill(ds);                
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch (Exception ex)
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show(ex.Message);
            }
        }

        /// <summary>
        /// Load Staff List
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void LoadStaffList()
        {
            try
            {
                myConnection.Open();
                SqlCommand sql_cmd = myConnection.CreateCommand();
                sql_cmd.CommandText = "SELECT StaffID, StaffName FROM [222_Staff]"
                    + " WHERE (([DepartmentID] = '231') OR ([DepartmentID] = '232')) AND (StopDate IS NULL) ORDER BY StaffID";
                SqlDataReader dataReader = sql_cmd.ExecuteReader();
                while (dataReader.Read())
                {                    
                    string[] fullname = dataReader["StaffName"].ToString().Trim().Split(' ');

                    CultureInfo cultureInfo = Thread.CurrentThread.CurrentCulture;
                    TextInfo textInfo = cultureInfo.TextInfo;
                    string txt = textInfo.ToTitleCase(fullname[fullname.Length - 1].ToLower());

                    StaffList.Add(txt + dataReader["StaffID"].ToString().Trim());                    
                }
                dataReader.Close();
                sql_cmd.Dispose();
                myConnection.Close();
            }
            catch (Exception ex)
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show(ex.Message);
            }
        }

        private void LoadSubSerial()
        {
            try
            {
                myConnection.Open();
                SqlCommand sql_cmd = myConnection.CreateCommand();
                sql_cmd.CommandText = "SELECT [SerialNo] FROM [231_F5Detail]"
                    + " WHERE ([FinishDate] > DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) - 500)";
                SqlDataReader dataReader = sql_cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    SubSerialList.Add(dataReader["SerialNo"].ToString());
                }
                dataReader.Close();
                sql_cmd.Dispose();
            }
            catch
            {                
                MessageBox.Show("Cannot load SubSerial.");
            }
            finally
            {
                myConnection.Close();
            }
        }

        public bool CreateForm()
        {
            bool ret = true;
            
            LoadSerialList();
            LoadStaffList();
            LoadSubSerial();

            if (string.IsNullOrEmpty(checkType.Value.ToString()))
            {
                ret = false;
            }
            else
            {
                this.formLabel.Text = checkType.Text.ToString();
                int index = 0;
                int x = 12;
                int y = 46;                
                try
                {
                    myConnection.Open();
                    SqlCommand sql_cmd = myConnection.CreateCommand();
                    sql_cmd.CommandText = "SELECT FunctionID, FunctionName, FunctionType, ShowForm, DefaultValue"
                        + " FROM [231_CheckFuntions]"
                        + " WHERE CheckTypeID = " + checkType.Value.ToString()
                        + " AND FunctionID <> 2"
                        + " ORDER BY FunctionID";
                    SqlDataReader dataReader = sql_cmd.ExecuteReader();
                    while (dataReader.Read())
                    {
                        int f_type = 4;
                        Int32.TryParse(dataReader["FunctionType"].ToString(), out f_type);
                        if (f_type == 0)
                        {
                            intem_AssemblyCheckSerial itemSerial = new intem_AssemblyCheckSerial();
                            itemSerial.funcID = Int32.Parse(dataReader["FunctionID"].ToString().Trim());
                            itemSerial.setLabel(dataReader["FunctionName"].ToString().Trim());
                            itemSerial.showform = (bool)dataReader["ShowForm"];
                            itemListSerial.Add(itemSerial);

                            if (itemSerial.showform)
                            {
                                itemSerial.Location = new System.Drawing.Point(x, y);
                                itemSerial.Name = "intem_AssemblyCheck" + index.ToString();
                                itemSerial.Size = new System.Drawing.Size(226, 51);
                                itemSerial.TabIndex = index + 1;                                
                                if (((index + 1) % 20) > 0)
                                {
                                    y += 50;
                                }
                                else
                                {
                                    x += 232;
                                    y = 46;                                    
                                }
                                this.Controls.Add(itemSerial);
                                index += 2;
                            }
                        }
                        else
                        {
                            if (f_type == 2)
                            {
                                intem_AssemblyCheckDate itemDate = new intem_AssemblyCheckDate();
                                itemDate.funcID = Int32.Parse(dataReader["FunctionID"].ToString().Trim());
                                itemDate.setLabel(dataReader["FunctionName"].ToString().Trim());
                                itemDate.showform = (bool)dataReader["ShowForm"];
                                itemListDate.Add(itemDate);

                                if (itemDate.showform)
                                {
                                    itemDate.Location = new System.Drawing.Point(x, y);
                                    itemDate.Name = "intem_AssemblyCheck" + index.ToString();
                                    itemDate.Size = new System.Drawing.Size(226, 27);
                                    itemDate.TabIndex = index + 1;
                                    if (((index + 1) % 20) > 0)
                                    {
                                        y += 25;
                                    }
                                    else
                                    {
                                        x += 232;
                                        y = 46;                                        
                                    }
                                    this.Controls.Add(itemDate);
                                    index++;
                                }
                            }
                            else
                            {
                                intem_AssemblyCheck item = new intem_AssemblyCheck();
                                item.funcID = Int32.Parse(dataReader["FunctionID"].ToString().Trim());
                                item.setLabel(dataReader["FunctionName"].ToString().Trim());
                                item.showform = (bool)dataReader["ShowForm"];
                                item.func_type = f_type;
                                if ((f_type != 1) && (f_type != 3))
                                {
                                    item.setDefaultValue(dataReader["DefaultValue"].ToString().Trim());
                                }                                
                                itemList.Add(item);

                                if (item.showform)
                                {
                                    item.Location = new System.Drawing.Point(x, y);
                                    item.Name = "intem_AssemblyCheck" + index.ToString();
                                    item.Size = new System.Drawing.Size(226, 27);
                                    item.TabIndex = index + 1;
                                    if (((index + 1) % 20) > 0)
                                    {
                                        y += 25;
                                    }
                                    else
                                    {
                                        x += 232;
                                        y = 46;                                        
                                    }
                                    this.Controls.Add(item);
                                    index++;
                                }
                            }
                        }                        
                    }
                    dataReader.Close();
                    sql_cmd.Dispose();
                }
                catch
                {
                    ret = false;
                    MessageBox.Show("Cannot create form.");
                }
                finally
                {
                    myConnection.Close();
                }

                if (itemList.Count > 0)
                {                    
                    for (int i = 0; i < this.itemList.Count; i++)
                    {
                        if (this.itemList[i].showform)
                        {
                            switch (this.itemList[i].func_type)
                            {
                                case 1:
                                    for (int j = 0; j < this.StaffList.Count; j++)
                                    {
                                        this.itemList[i].addValue(this.StaffList[j]);
                                    }
                                    break;
                                case 3:
                                    for (int j = 0; j < this.SubSerialList.Count; j++)
                                    {
                                        this.itemList[i].addValue(this.SubSerialList[j]);
                                    }
                                    break;                                
                                default:
                                    try
                                    {
                                        myConnection.Open();
                                        SqlCommand sql_cmd = myConnection.CreateCommand();
                                        sql_cmd.CommandText = "SELECT [CheckValue] FROM [231_CheckDetails]"
                                            + " WHERE CheckTypeID = " + this.checkType.Value.ToString()
                                            + " AND CheckFuntionID = " + this.itemList[i].funcID.ToString()
                                            + " AND [CheckValue] IS NOT NULL AND [CheckValue] <> ''"                                            
                                            + " GROUP BY [CheckValue]";
                                        SqlDataReader dataReader = sql_cmd.ExecuteReader();
                                        while (dataReader.Read())
                                        {
                                            this.itemList[i].addValue(dataReader["CheckValue"].ToString().Trim());
                                        }
                                        dataReader.Close();
                                        sql_cmd.Dispose();
                                    }
                                    catch
                                    {

                                    }
                                    finally
                                    {
                                        myConnection.Close();
                                    }
                                    break;
                            }                            
                        }
                    }
                }

                if (this.itemListSerial.Count > 0)
                {
                    for (int i = 0; i < this.itemListSerial.Count; i++)
                    {
                        this.itemListSerial[i].CreateData(this.ds);
                    }
                }
            }
            return ret;
        }

        private void buttonCancel_Click(object sender, EventArgs e)
        {
            this.Dispose();
        }

        private void buttonAdd_Click(object sender, EventArgs e)
        {
            string err = null;
            myConnection.Open();
            SqlCommand cmd_sql;
            SqlTransaction tran = myConnection.BeginTransaction();
            try
            {                
                string sql = null;
                for (int i = 0; i < this.itemListSerial.Count; i++)
                {
                    this.main_serial = this.itemListSerial[i].getSerial();
                    string partno = this.itemListSerial[i].getPartNo();

                    if (string.IsNullOrEmpty(err))
                    {
                        if (string.IsNullOrEmpty(this.main_serial))
                        {
                            err = "Please select " + this.itemListSerial[i].getFuncName();
                        }
                        else
                        {
                            sql = "UPDATE [231_CheckDetails] SET RRCSerialNo= '" + this.main_serial + "'"
                                + " WHERE CheckTypeID = " + this.checkType.Value.ToString()                                
                                + " AND RRCSerialNo = '" + this.serial + "'";
                            cmd_sql = new SqlCommand(sql, myConnection);
                            cmd_sql.Transaction = tran;
                            cmd_sql.ExecuteNonQuery();

                            sql = "UPDATE [231_CheckDetails] SET CheckValue = '" + this.main_serial + "'"
                                + " WHERE CheckTypeID = " + this.checkType.Value.ToString()
                                + " AND CheckFuntionID = " + this.itemListSerial[i].funcID
                                + " AND RRCSerialNo = '" + this.main_serial + "'";
                            cmd_sql = new SqlCommand(sql, myConnection);
                            cmd_sql.Transaction = tran;
                            cmd_sql.ExecuteNonQuery();
                            
                            sql = "UPDATE [231_CheckDetails]"
                                + " SET CheckValue = N'" + this.itemListSerial[i].getPartNo() + "'"
                                + " WHERE CheckTypeID = " + this.checkType.Value.ToString()
                                + " AND CheckFuntionID = 2"
                                + " AND RRCSerialNo = '" + this.main_serial + "'";
                            cmd_sql = new SqlCommand(sql, myConnection);
                            cmd_sql.Transaction = tran;
                            cmd_sql.ExecuteNonQuery();
                        }
                    }
                }                

                if (string.IsNullOrEmpty(err))
                {
                    for (int i = 0; i < this.itemListDate.Count; i++)
                    {
                        if (string.IsNullOrEmpty(err))
                        {
                            if (string.IsNullOrEmpty(this.itemListDate[i].getValue()))
                            {
                                err = "Please select " + this.itemListDate[i].getFuncName();
                            }
                            else
                            {
                                sql = "UPDATE [231_CheckDetails]"
                                    + " SET CheckValue = '" + this.itemListDate[i].getValue() + "'"
                                    + " WHERE RRCSerialNo = '" + this.main_serial + "'"
                                    + " AND CheckTypeID = " + this.checkType.Value.ToString()
                                    + " AND CheckFuntionID = " + this.itemListDate[i].funcID.ToString();
                                cmd_sql = new SqlCommand(sql, myConnection);
                                cmd_sql.Transaction = tran;
                                cmd_sql.ExecuteNonQuery();
                            }
                        }
                    }
                }

                if (string.IsNullOrEmpty(err))
                {
                    for (int i = 0; i < this.itemList.Count; i++)
                    {
                        if (string.IsNullOrEmpty(err))
                        {
                            if (((string.IsNullOrEmpty(this.itemList[i].getValue())) && (this.itemList[i].func_type == 3)) ||
                                ((string.IsNullOrEmpty(this.itemList[i].getValue())) && (this.itemList[i].func_type == 1)))
                            {
                                err = "Please select " + this.itemList[i].getFuncName();
                            }
                            else
                            {
                                sql = "UPDATE [231_CheckDetails] SET CheckValue = N'" + this.itemList[i].getValue() + "'"
                                    + " WHERE RRCSerialNo = '" + this.main_serial + "'"
                                    + " AND CheckTypeID = " + this.checkType.Value.ToString()
                                    + " AND CheckFuntionID = " + this.itemList[i].funcID.ToString();
                                cmd_sql = new SqlCommand(sql, myConnection);
                                cmd_sql.Transaction = tran;
                                cmd_sql.ExecuteNonQuery();
                            }
                        }
                    }
                }

                if (string.IsNullOrEmpty(err))
                {
                    tran.Commit();
                    MessageBox.Show("Update Assembly Check successfully.");
                    this.Dispose();
                }
                else
                {
                    tran.Rollback();
                    MessageBox.Show(err);
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                tran.Rollback();                
            }
            finally
            {
                myConnection.Close();
            }
        }
    }
}
